﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[MonthClose] 
	-- Add the parameters for the stored procedure here
	@DataDoc DATETIME,
	@DeLaData DATETIME,
	@PanaLaData DATETIME
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @NrDocAdd int
	DECLARE @LastNrDoc bigint
	SET NOCOUNT ON;
    BEGIN TRAN
	INSERT INTO [RulajeDoc]
	([ValoareLei],[ValoareValuta],[NumarDoc],[DataDoc],[NumarNota],[TipFormula],[IDJurnal]
	,[Observatii],[xUser],[xHost])   
	SELECT
	ValoareLei,
	0 AS ValoareValuta,
	NumarDoc+RowNumber NumarDoc,
	@DataDoc AS DataDoc,
	'IC' AS NumarNota,
	2 AS TipFormula,
	IdJurnal,
	CASE WHEN Cont = '6' THEN 'Inchidere cont de cheltuieli' WHEN Cont='7' THEN 'Inchidere cont de venituri' ELSE '' END AS Observatii,
	system_user AS xUser
	,host_name() AS xHost
	FROM (SELECT 
		ABS(SUM(soldd)) ValoareLei,
		Cont,IDJurnal,(SELECT Max(NumarDoc) FROm RulajeDoc) NumarDoc,
		ROW_NUMBER() OVER (ORDER BY IDJurnal) RowNumber FROM
			(SELECT SUM(a.ValoareLei) soldd, dbo.GetContClass(ContDebit) Cont,IDJurnal FROM Rulaje a
			JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
			WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData
			GROUP BY dbo.GetContClass(ContDebit),IDJurnal
			UNION
			SELECT -SUM(a.ValoareLei) soldd, dbo.GetContClass(ContCredit) Cont,IDJurnal FROM Rulaje a
			JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
			WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData
			GROUP BY dbo.GetContClass(ContCredit),IDJurnal) t1
		WHERE Cont>='6' AND Cont<='7' AND soldd<>0
		GROUP BY Cont,IDJurnal) t2
	WHERE ValoareLei<>0
	SET @NrDocAdd = @@ROWCOUNT	
	PRINT(@@IDENTITY)
	PRINT(@NrDocAdd)
	PRINT('Document')
	
    PRINT(@LastNrDoc)
		IF @@ERROR <>0  GOTO Eroare
		INSERT INTO [Rulaje]
		([ContDebit],[ContCredit],[CodValuta],[IDDocument] ,[ValoareValuta],[Curs],[ValoareLei],[DataDocJust]
			,[TipDocJust] ,[NumarDocJust] ,[ExplicatiiDocJust] ,[NrCrt])
			SELECT 
			CASE WHEN SCont = '7' THEN Cont WHEN SCont='6' THEN '121' ELSE 'N/A' END ContDebit,
			CASE WHEN SCont = '7' THEN '121' WHEN SCont='6' THEN Cont ELSE 'N/A' END ContCredit,
			0 AS CodValuta,
			NumarDoc-@NrDocAdd+Rownumber AS IDDocument,
			0 AS ValoareValuta,
			0 AS Curs,
			ValoareLei,
			@DataDoc AS DataDocJust,
			'IC' AS TipDocJust,
			0 AS NumarDocJust,
			''  AS ExplicatiiDocJust,
			0 AS NrCrt
			FROM (SELECT 
					ABS(SUM(soldd)) ValoareLei,SCont,
					Cont,IDJurnal, @@IDENTITY AS NumarDoc,
					DENSE_RANK() OVER (ORDER BY IDJurnal,SCont) RowNumber FROM
						(SELECT SUM(a.ValoareLei) soldd, ContDebit Cont,dbo.GetContClass(ContDebit) SCont,IDJurnal FROM Rulaje a
						JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
						WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData
						GROUP BY ContDebit,dbo.GetContClass(ContDebit),IDJurnal
						UNION
						SELECT -SUM(a.ValoareLei) soldd, ContCredit Cont,dbo.GetContClass(ContCredit) SCont,IDJurnal FROM Rulaje a
						JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
						WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData
						GROUP BY ContCredit,dbo.GetContClass(ContCredit),IDJurnal) t1
					WHERE dbo.GetContClass(Cont)>='6' AND dbo.GetContClass(Cont)<='7' AND soldd<>0
					GROUP BY Cont,SCont,IDJurnal) t2
		WHERE ValoareLei<>0
		IF @@ERROR <>0  GOTO Eroare
		COMMIT TRAN
	GOTO Gata
	Eroare:
		ROLLBACK
		RAISERROR('Eroare la modificarea articolului ',16,-1)
		return
	Gata:
END